﻿CREATE VIEW [util].[DbBackupStatus]
AS
SELECT TOP 1000
		CAST(SERVERPROPERTY('ServerName') AS varchar(30)) AS [Server Name]
		,LEFT(db.[name],30) AS [Database Name]
		,(CASE
			WHEN ((db.[status] & 8) = 8) THEN 'Simple'
			WHEN ((db.[status] & 4) = 4) THEN 'Bulk-Logged'
			ELSE 'Full'
		END) AS [Recovery Model]
		,LEFT(ISNULL(
			(
			SELECT
				CONVERT(varchar,MAX(lbs.[backup_finish_date]),20)
				+ ' (' + CAST(DATEDIFF(hour,ISNULL(MAX(lbs.[backup_finish_date]),''),GETDATE()) AS varchar)  + ' hrs)'
			FROM [msdb].dbo.backupset AS lbs --WITH(INDEX(backupsetfinishdate))
			WHERE (lbs.[database_name] = db.[name] COLLATE DATABASE_DEFAULT)
				AND(lbs.[type] = 'D')
			)
		,'BACKUP REQUIRED'),31) AS [Last Database Backup]
		,LEFT((CASE
			WHEN ((db.[status] & 8) = 8) THEN ''
			ELSE ISNULL(
				(
					SELECT
						CONVERT(varchar,MAX(lbs.[backup_finish_date]),20)
						+ ' (' + CAST(DATEDIFF(hour,ISNULL(MAX(lbs.[backup_finish_date]),''),GETDATE()) AS varchar)  + ' hrs)'
					FROM [msdb].dbo.backupset AS lbs --WITH(INDEX(backupsetfinishdate))
					WHERE (lbs.[database_name] = db.[name] COLLATE DATABASE_DEFAULT)
						AND(lbs.[type] = 'L')
				),'LOG BACKUP REQUIRED')
		END),31) AS [Last Transaction Log Backup]
	FROM [master].dbo.sysdatabases AS db
	WHERE (db.[name] <> 'tempdb')
		AND((db.[status] & 1024) = 0) --Not Read Only
		AND((db.[status] & 32) = 0) --In Recovered Status
	GROUP BY
		db.[dbid]
		,db.[name]
		,db.[status]
	ORDER BY
		db.[name];